﻿using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections;
using System.Data;

namespace G.Core.Common
{
    public class UtilityOracle
    {
        public OracleConnection Connection;
        public UtilityOracle()
        {
            string db_link =
                "Data Source = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.3)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = ORCL))); User Id = ZLHIS; Password = HIS";
            this.Connection = new OracleConnection(db_link);
        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="sqlString">查询语句</param>
        /// <returns>DataSet</returns>
        public DataSet QueryDataSet(string sqlString)
        {
            using (OracleDataAdapter command = new OracleDataAdapter(sqlString, this.Connection))
            {
                try
                {
                    if (this.Connection.State == ConnectionState.Closed)
                        this.Connection.Open();
                    DataSet ds = new DataSet();
                    command.Fill(ds, "ds");
                    return ds;
                }
                catch (OracleException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        /// <summary>
        /// 执行查询语句，返回DataTable
        /// </summary>
        /// <param name="sqlString">查询语句</param>
        /// <returns></returns>
        public DataTable QueryDataTable(string sqlString)
        {
            using (OracleDataAdapter command = new OracleDataAdapter(sqlString, this.Connection))
            {
                try
                {
                    if (this.Connection.State == ConnectionState.Closed)
                        this.Connection.Open();
                    DataSet ds = new DataSet();
                    command.Fill(ds, "ds");
                    return ds.Tables[0];
                }
                catch (OracleException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        /// <summary>
        /// 执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="sqlString">SQL语句</param>
        /// <returns>影响的记录数</returns>
        public int ExecuteSql(string sqlString)
        {
            using (OracleCommand cmd = new OracleCommand(sqlString, this.Connection))
            {
                try
                {
                    if (this.Connection.State == ConnectionState.Closed)
                        this.Connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="sqlStringList">多条SQL语句</param>		
        public void ExecuteSqlTran(ArrayList sqlStringList)
        {
            using (OracleConnection conn = this.Connection)
            {
                if (this.Connection.State == ConnectionState.Closed)
                    this.Connection.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                OracleTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < sqlStringList.Count; n++)
                    {
                        string strsql = sqlStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql; 
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (OracleException ex)
                {
                    tx.Rollback();
                    throw new Exception(ex.Message);
                }
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="sql">存储过程名称</param>
        public void RunProcedure(string sql)
        {
            using (OracleConnection conn = this.Connection)
            {
                if (this.Connection.State == ConnectionState.Closed)
                    this.Connection.Open();
                try
                {
                    OracleCommand cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }
    }
}
